package com.test.wog.dao;

import com.test.wog.bean.RegularBus;
import com.test.wog.util.GeometryTrimUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @author LX
 * @createDate 2020-01-27
 * @desc
 */
@Repository
public class RegularBusDAOImpl implements RegularBusDAO {

    @Autowired
    JdbcTemplate jdbc;
    @Autowired
    GeometryTrimUtil gtu;
    @Override
    public List<RegularBus> getRegularBusByOriDestType(String ori, String dest, String type) {
        String sql = "select id, origin, destination, " + gtu.trimLine("ST_ASTEXT(geom)") + " as geom from regular_bus_route where origin = ? and destination = ? and type = ?";
        List<RegularBus> regularBusList = jdbc.query(
                sql,
                new Object[]{ori, dest, type},
                new BeanPropertyRowMapper(RegularBus.class)
        );
        return regularBusList;
    }

    @Override
    public List<RegularBus> getRegularBusByIdAndTime(int id, String startTime) {//寻找编号为id，发车时间小于等于startTime的班车车次
        String sql = "select id, start_time, " + gtu.trimLine("ST_ASTEXT(geom)") + " as geom from regular_bus_time where id = ? and start_time < ? order by start_time desc";
        List<RegularBus> regularBusList = jdbc.query(
                sql,
                new Object[]{id, startTime},
                new BeanPropertyRowMapper(RegularBus.class)
        );
        return regularBusList;
    }

    @Override
    public int insertRegularBus(RegularBus regularBus) {
        String syhq_to_bqfwq = "115.962351 40.439899 ,115.96146 40.439744 ,115.959121 40.436576 ,115.956332 40.436028 ,115.95115 40.435277 ,115.945839 40.434599 ,115.939541 40.433766 ,115.936591 40.432982 ,115.932997 40.431684 ,115.92628 40.488671";
        //世园海泉->板泉
        int insertNum = 0;
        regularBus = regularBus.toBuilder().
                origin(35).
                destination(19).
                geom("LINESTRING(" + syhq_to_bqfwq + ")").
                build();
        System.out.println(regularBus.toString());
        String sql = "INSERT into regular_bus_route(origin, destination, geom) values(?,?,ST_GEOMFROMTEXT(?))";
        insertNum += jdbc.update(sql, regularBus.getOrigin(), regularBus.getDestination(), regularBus.getGeom());
        return insertNum;
    }

    public static void main(String args[]) {
        String bqfwq_to_gshx = "115.926297 40.488696,115.926643 40.493683,115.925785 40.497077,115.92527 40.500079,115.924068 40.503082,115.923553 40.503995,115.922695 40.505431,115.921322 40.508009,115.919949 40.511011,115.917889 40.51336,115.91394 40.517014,115.909477 40.519885,115.906387 40.521712,115.903469 40.522756,115.900894 40.523539,115.896946 40.524191,115.889908 40.523799,115.886818 40.523539,115.880638 40.521712,115.875145 40.519624,115.872227 40.518971,115.866047 40.515709,115.864502 40.514012,115.863472 40.511924,115.863129 40.508661,115.862442 40.506312,115.858837 40.499786,115.856949 40.498219,115.846134 40.495478,115.804303 40.50703,115.798294 40.508746,115.79819 40.508859,115.797669 40.50947,115.797669 40.50947,115.797083 40.510156,115.796884 40.510343,115.796753 40.510425,115.795794 40.510816,115.795794 40.510816,115.793772 40.511445,115.792904 40.511688,115.792713 40.511766,115.792461 40.511888,115.792062 40.512109,115.791871 40.51224,115.791658 40.512409,115.791497 40.512556,115.791393 40.512674,115.791337 40.512765,115.791293 40.512865,115.791272 40.512973,115.791237 40.513407,115.791194 40.513611,115.791155 40.513694,115.791094 40.513789,115.790864 40.514002,115.79076 40.514076,115.790599 40.514145,115.790399 40.514219,115.788698 40.514657,115.788555 40.514714,115.788438 40.514779,115.788342 40.514861,115.788303 40.514905,115.788247 40.515004,115.788242 40.515122,115.788281 40.515213,115.788351 40.515265,115.788411 40.515291,115.788411 40.515291,115.788481 40.515304,115.788655 40.515299,115.788906 40.51526,115.789839 40.515087,115.790069 40.515022,115.790069 40.515022,115.790304 40.514926,115.790356 40.514913,115.790464 40.514913,115.790547 40.514935,115.790616 40.515,115.790647 40.515065,115.790642 40.515143,115.790642 40.515143,115.79056 40.51523,115.79023 40.51546,115.790109 40.515564,115.789948 40.515747,115.78944 40.516398,115.78944 40.516398,115.789379 40.516515,115.789379 40.516515,115.78928 40.516997,115.78928 40.517127,115.789306 40.517174,115.789549 40.517461,115.789722 40.517773,115.789783 40.518069,115.789705 40.518403,115.789514 40.518906,115.789401 40.519293,115.789384 40.519562,115.789405 40.519692,115.789527 40.520074,115.789709 40.520851,115.789809 40.521094,115.789939 40.521324,115.790122 40.521493,115.790425 40.52158,115.790425 40.52158,115.790477 40.521615,115.790477 40.521615,115.790516 40.521641,115.790543 40.521706,115.790573 40.521953,115.79056 40.522131,115.790521 40.522292,115.790521 40.522292,115.790339 40.522721,115.79033 40.523268,115.790109 40.524987,115.790109 40.525312,115.790135 40.525477,115.790195 40.525586,115.790786 40.526189,115.790981 40.526532,115.791393 40.526927,115.791723 40.527166,115.791836 40.527418,115.791875 40.52786,115.791936 40.528138,115.792036 40.528325,115.792205 40.528524,115.792396 40.528685,115.792899 40.529036,115.792969 40.52934,115.792908 40.529501,115.792817 40.529605,115.792812 40.529605,115.792552 40.529583,115.792435 40.529549,115.792339 40.529492,115.791858 40.529184,115.791523 40.529015,115.791215 40.528763,115.791141 40.528715,115.791033 40.528707,115.790924 40.528711,115.79089 40.528763,115.790898 40.528824,115.790959 40.528911,115.791172 40.52908,115.791484 40.529444,115.791784 40.529714,115.791862 40.529831,115.791905 40.529957,115.791914 40.530078,115.791879 40.530187,115.791758 40.530382,115.791597 40.530556,115.791233 40.530911,115.791072 40.531124,115.790955 40.531367,115.790699 40.532257,115.790699 40.532257,115.79059 40.532799,115.790768 40.533859,115.790807 40.534171,115.790968 40.534258,115.791098 40.534219,115.79125 40.533268,115.791332 40.533125,115.791454 40.533099,115.791602 40.533103,115.791645 40.533299,115.791424 40.533594,115.791424 40.534058,115.791345 40.534492,115.791345 40.534622,115.791402 40.534753,115.791584 40.535,115.791927 40.535417,115.792036 40.535851,115.792105 40.53625,115.792296 40.53651,115.792431 40.536541,115.79253 40.536506,115.79253 40.536302,115.792253 40.535747,115.792257 40.535564,115.792339 40.53546,115.79247 40.535469,115.792578 40.535573,115.792691 40.536111,115.792782 40.536302,115.793012 40.536428,115.793364 40.536463,115.793937 40.536489,115.794149 40.536554,115.794887 40.536918,115.795295 40.537066,115.79569 40.537183,115.795959 40.537196,115.796063 40.537122,115.796016 40.537036,115.795816 40.536923,115.79513 40.536727,115.794818 40.536606,115.794206 40.536289,115.794145 40.536211,115.794145 40.536172,115.79418 40.536102,115.79424 40.536072,115.794323 40.536055,115.794431 40.536085,115.794531 40.536185,115.794783 40.53638,115.795078 40.536515,115.795929 40.536697,115.796315 40.536884,115.797261 40.537457,115.797765 40.537739,115.798138 40.537917,115.798416 40.537956,115.798728 40.537873,115.799054 40.537613,115.799536 40.537491,115.800009 40.53753,115.800534 40.537778,115.800946 40.538069,115.801046 40.538338,115.801107 40.538676,115.801059 40.538928,115.801107 40.539336,115.801107 40.539336,115.80125 40.539323,115.801398 40.53924,115.801528 40.538342,115.801463 40.538099,115.801302 40.537661,115.800981 40.537352,115.800538 40.537127,115.799371 40.53694,115.799288 40.536897,115.799227 40.536775,115.799275 40.536671,115.799362 40.536649,115.799518 40.536654,115.800816 40.536988,115.801289 40.53724,115.801549 40.537548,115.802266 40.53849,115.802326 40.538602,115.802231 40.53947,115.802444 40.539887,115.802917 40.540304,115.804236 40.54092,115.804236 40.54092,115.80431 40.540959,115.804427 40.541046,115.804427 40.541046,115.804518 40.541124,115.804631 40.541597,115.80467 40.541641,115.804883 40.541797,115.804883 40.541797,115.805213 40.541966,115.805686 40.542075,115.805846 40.542127,115.806259 40.542461,115.806432 40.542548,115.806602 40.542582,115.806966 40.542609,115.807569 40.5426,115.807995 40.542565,115.808173 40.542569,115.808477 40.542617,115.809041 40.542743,115.809349 40.542852,115.809692 40.543025,115.810004 40.543142,115.81033 40.543229,115.810621 40.543277,115.81076 40.54332,115.81089 40.543381,115.811102 40.54355,115.811207 40.543655,115.811385 40.54388,115.811649 40.544141,115.811858 40.544262,115.812161 40.544371,115.81227 40.54444,115.8123 40.544479,115.8123 40.544479,115.812344 40.544531,115.812361 40.544635,115.812335 40.544913,115.812174 40.545508,115.811862 40.54648,115.811858 40.54648,115.812261 40.546623,115.812339 40.546667,115.812344 40.546714,115.811984 40.547062";
        //板泉->场馆
        String syhq_to_bqfwq = "115.962351 40.439899 ,115.96146 40.439744 ,115.959121 40.436576 ,115.956332 40.436028 ,115.95115 40.435277 ,115.945839 40.434599 ,115.939541 40.433766 ,115.936591 40.432982 ,115.932997 40.431684 ,115.92628 40.488671";
        //世园海泉->板泉
        String yqdjc_to_jsgc = "115.820783 40.462938,115.823932 40.461432,115.824978 40.462203,115.842155 40.474809,115.845146 40.476897,115.846846 40.477537,115.849346 40.477897,115.860847 40.479096,115.862103 40.479341,115.890459 40.494309,115.903194 40.507239,115.91786 40.513482,115.91394 40.517014,115.909477 40.519885,115.906387 40.521712,115.903469 40.522756,115.900894 40.523539,115.896946 40.524191,115.889908 40.523799,115.886818 40.523539,115.880638 40.521712,115.875145 40.519624,115.872227 40.518971,115.866047 40.515709,115.864502 40.514012,115.863472 40.511924,115.863129 40.508661,115.862442 40.506312,115.858837 40.499786,115.856949 40.498219,115.846134 40.495478,115.804303 40.50703,115.798294 40.508746,115.79819 40.508859,115.797669 40.50947,115.797669 40.50947,115.797083 40.510156,115.796884 40.510343,115.796753 40.510425,115.795794 40.510816,115.795794 40.510816,115.793772 40.511445,115.792904 40.511688,115.792713 40.511766,115.792461 40.511888,115.792062 40.512109,115.791871 40.51224,115.791658 40.512409,115.791497 40.512556,115.791393 40.512674,115.791337 40.512765,115.791293 40.512865,115.791272 40.512973,115.791237 40.513407,115.791194 40.513611,115.791155 40.513694,115.791094 40.513789,115.790864 40.514002,115.79076 40.514076,115.790599 40.514145,115.790399 40.514219,115.788698 40.514657,115.788555 40.514714,115.788438 40.514779,115.788342 40.514861,115.788303 40.514905,115.788247 40.515004,115.788242 40.515122,115.788281 40.515213,115.788351 40.515265,115.788411 40.515291,115.788411 40.515291,115.788481 40.515304,115.788655 40.515299,115.788906 40.51526,115.789839 40.515087,115.790069 40.515022,115.790069 40.515022,115.790304 40.514926,115.790356 40.514913,115.790464 40.514913,115.790547 40.514935,115.790616 40.515,115.790647 40.515065,115.790642 40.515143,115.790642 40.515143,115.79056 40.51523,115.79023 40.51546,115.790109 40.515564,115.789948 40.515747,115.78944 40.516398,115.78944 40.516398,115.789379 40.516515,115.789379 40.516515,115.78928 40.516997,115.78928 40.517127,115.789306 40.517174,115.789549 40.517461,115.789722 40.517773,115.789783 40.518069,115.789705 40.518403,115.789514 40.518906,115.789401 40.519293,115.789384 40.519562,115.789405 40.519692,115.789527 40.520074,115.789709 40.520851,115.789809 40.521094,115.789939 40.521324,115.790122 40.521493,115.790425 40.52158,115.790425 40.52158,115.790477 40.521615,115.790477 40.521615,115.790516 40.521641,115.790543 40.521706,115.790573 40.521953,115.79056 40.522131,115.790521 40.522292,115.790521 40.522292,115.790339 40.522721,115.79033 40.523268,115.790109 40.524987,115.790109 40.525312,115.790135 40.525477,115.790195 40.525586,115.790786 40.526189,115.790981 40.526532,115.791393 40.526927,115.791723 40.527166,115.791836 40.527418,115.791875 40.52786,115.791936 40.528138,115.792036 40.528325,115.792205 40.528524,115.792396 40.528685,115.792899 40.529036,115.792969 40.52934,115.792908 40.529501,115.792817 40.529605,115.792812 40.529605,115.792552 40.529583,115.792435 40.529549,115.792339 40.529492,115.791858 40.529184,115.791523 40.529015,115.791215 40.528763,115.791141 40.528715,115.791033 40.528707,115.790924 40.528711,115.79089 40.528763,115.790898 40.528824,115.790959 40.528911,115.791172 40.52908,115.791484 40.529444,115.791784 40.529714,115.791862 40.529831,115.791905 40.529957,115.791914 40.530078,115.791879 40.530187,115.791758 40.530382,115.791597 40.530556,115.791233 40.530911,115.791072 40.531124,115.790955 40.531367,115.790699 40.532257,115.790699 40.532257,115.79059 40.532799,115.790768 40.533859,115.790807 40.534171,115.790968 40.534258,115.791098 40.534219,115.79125 40.533268,115.791332 40.533125,115.791454 40.533099,115.791602 40.533103,115.791645 40.533299,115.791424 40.533594,115.791424 40.534058,115.791345 40.534492,115.791345 40.534622,115.791402 40.534753,115.791584 40.535,115.791927 40.535417,115.792036 40.535851,115.792105 40.53625,115.792296 40.53651,115.792431 40.536541,115.79253 40.536506,115.79253 40.536302,115.792253 40.535747,115.792257 40.535564,115.792339 40.53546,115.79247 40.535469,115.792578 40.535573,115.792691 40.536111,115.792782 40.536302,115.793012 40.536428,115.793364 40.536463,115.793937 40.536489,115.794149 40.536554,115.794887 40.536918,115.795295 40.537066,115.79569 40.537183,115.795959 40.537196,115.796063 40.537122,115.796016 40.537036,115.795816 40.536923,115.79513 40.536727,115.794818 40.536606,115.794206 40.536289,115.794145 40.536211,115.794145 40.536172,115.79418 40.536102,115.79424 40.536072,115.794323 40.536055,115.794431 40.536085,115.794531 40.536185,115.794783 40.53638,115.795078 40.536515,115.795929 40.536697,115.796315 40.536884,115.797261 40.537457,115.797765 40.537739,115.798138 40.537917,115.798416 40.537956,115.798728 40.537873,115.799054 40.537613,115.799536 40.537491,115.800009 40.53753,115.800534 40.537778,115.800946 40.538069,115.801046 40.538338,115.801107 40.538676,115.801059 40.538928,115.801107 40.539336,115.801107 40.539336,115.80125 40.539323,115.801398 40.53924,115.801528 40.538342,115.801463 40.538099,115.801302 40.537661,115.800981 40.537352,115.800538 40.537127,115.799371 40.53694,115.799288 40.536897,115.799227 40.536775,115.799275 40.536671,115.799362 40.536649,115.799518 40.536654,115.800816 40.536988,115.801289 40.53724,115.801549 40.537548,115.802266 40.53849,115.802326 40.538602,115.802231 40.53947,115.802444 40.539887,115.802917 40.540304,115.804236 40.54092,115.804236 40.54092,115.80431 40.540959,115.804427 40.541046,115.804427 40.541046,115.804518 40.541124,115.804631 40.541597,115.80467 40.541641,115.804883 40.541797,115.804883 40.541797,115.805213 40.541966,115.805686 40.542075,115.805846 40.542127,115.806259 40.542461,115.806432 40.542548,115.806602 40.542582,115.806966 40.542609,115.807569 40.5426,115.807995 40.542565,115.808173 40.542569,115.808477 40.542617,115.809041 40.542743,115.809349 40.542852,115.809692 40.543025,115.810004 40.543142,115.81033 40.543229,115.810621 40.543277,115.81076 40.54332,115.81089 40.543381,115.811102 40.54355,115.811207 40.543655,115.811385 40.54388,115.811649 40.544141,115.811858 40.544262,115.812161 40.544371,115.81227 40.54444,115.8123 40.544479,115.8123 40.544479,115.812344 40.544531,115.812361 40.544635,115.812335 40.544913,115.812174 40.545508,115.811862 40.54648,115.811858 40.54648,115.812261 40.546623,115.812339 40.546667,115.812344 40.546714,115.811984 40.547062";
        //度假村->场馆
        //RegularBus regularBus = RegularBus.builder().origin("good").build();
//        System.out.println(insertRegularBus(new RegularBus()));
        //System.out.println(yqdjc_to_jsgc.replace(",", " ").replace("-", ","));
    }


}
